﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using Tms.Model;
using Util.Common;
using Util.DBUtility;

namespace Tms.DAL
{
    public partial class patient
    {
        //表名
        private string table_name = GlobalCache.DatabasePrefix + "patient";
                
        //是否存在
        public bool Exists(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from " + table_name);
            sql.Append(" where id=" + id);
            
            return DbHelperOleDb.Exists(sql.ToString());
        }
        public bool Exists(string strWhere)    //20191216 lxj
        {
            //校验
            if (strWhere == null) strWhere = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 id from " + table_name);

            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            return DbHelperOleDb.Exists(sql.ToString());

        }
        public bool ExistsNo(string patientNo)
        {
            return Exists("patient_no = '" + patientNo + "'");
        }

        //增加一条记录，返回自增主键值
        public int Add(Model.patient model)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数

            sql.Append("insert into " + table_name + "(");

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            List<OleDbParameter> paras = new List<OleDbParameter>();
            
            foreach (PropertyInfo ppt in ppts)
            {
                //排除自增主键字段
                if (!ppt.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (ppt.GetValue(model, null) != null)
                    {
                        str1.Append(ppt.Name + ",");//拼接字段
                        str2.Append("@" + ppt.Name + ",");//声明参数

                        if (ppt.Name.Equals("add_time"))//时间字段，特殊处理
                        {
                            OleDbParameter para = new OleDbParameter("@add_time", OleDbType.Date); 
                            para.Value = model.add_time;
                            paras.Add(para);//对参数赋值
                        }
                        else
                        {
                            paras.Add(new OleDbParameter("@" + ppt.Name, ppt.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
            }

            sql.Append(str1.ToString().Trim(','));
            sql.Append(") values (");
            sql.Append(str2.ToString().Trim(','));
            sql.Append(") ");

            //执行 insert，并返回新的自增主键值
            object obj = DbHelperOleDb.GetSingle(sql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //更新一条记录
        public bool Update(Model.patient model)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();

            sql.Append("update " + table_name + " set ");

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            List<OleDbParameter> paras = new List<OleDbParameter>();
            
            foreach (PropertyInfo ppt in ppts)
            {
                //排除主键字段（因为不允许更新主键）
                if (!ppt.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (ppt.GetValue(model, null) != null)
                    {
                        str1.Append(ppt.Name + "=@" + ppt.Name + ",");//声明参数

                        if (ppt.Name.Equals("add_time"))//时间字段，特殊处理
                        {
                            OleDbParameter para = new OleDbParameter("@add_time", OleDbType.Date); 
                            para.Value = model.add_time;
                            paras.Add(para);//对参数赋值
                        }
                        else
                        {
                            paras.Add(new OleDbParameter("@" + ppt.Name, ppt.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
            }

            sql.Append(str1.ToString().Trim(','));
            sql.Append(" where id=@id ");
            paras.Add(new OleDbParameter("@id", model.id));

            int rtn = DbHelperOleDb.ExecuteSql(sql.ToString(), paras.ToArray());
            return rtn > 0;
        }

        //删除一条记录
        public bool Delete(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + table_name);
            sql.Append(" where id=@id");
            OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.BigInt,4)};
            parameters[0].Value = id;

            int rtn = DbHelperOleDb.ExecuteSql(sql.ToString(), parameters);
            return rtn > 0;
        }
        public bool Delete(string strWhere)
        {
            //校验
            if (strWhere == null) strWhere = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + table_name);
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            int rtn = DbHelperOleDb.ExecuteSql(sql.ToString());
            return rtn > 0;
        }
        public bool DeleteNo(string patientNo)
        {
            return Delete("patient_no = '" + patientNo + "'");
        }

        //将一行数据转换为实体对象
        public Model.patient DataRowToModel(DataRow row)
        {
            Model.patient model = new Model.patient();
            if (row != null)
            {
                //利用反射获取实体对象的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //根据列名获取具体属性
                    PropertyInfo ppt = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (ppt != null && row[i] != DBNull.Value)
                    {
                        if (ppt.CanWrite)  //20200520
                            ppt.SetValue(model, row[i], null);//设置属性值
                    }
                }
            }
            return model;
        }

        //获取一个对象实体
        public Model.patient GetModel(int id)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.patient model = new Model.patient();

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            foreach (PropertyInfo ppt in ppts)
            {
                str1.Append(ppt.Name + ",");//拼接字段
            }

            sql.Append("select top 1 " + str1.ToString().Trim(','));
            sql.Append(" from " + table_name);
            sql.Append(" where id=@id");
            OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.BigInt,4)};
            parameters[0].Value = id;
            DataTable dt = DbHelperOleDb.Query(sql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public Model.patient GetModel(string patientNo)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.patient model = new Model.patient();

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            foreach (PropertyInfo ppt in ppts)
            {
                str1.Append(ppt.Name + ",");//拼接字段
            }
            sql.Append("select top 1 " + str1.ToString().Trim(','));
            sql.Append(" from " + table_name);
            sql.Append(" where patient_no = '" + patientNo + "'");
            
            DataTable dt = DbHelperOleDb.Query(sql.ToString()).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        //获取前几行数据
        public DataSet GetList(int topNum, string strWhere, string strOrder)
        {
            //校验
            if (strWhere == null) strWhere = "";
            if (strOrder == null) strOrder = "";

            //开始
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (topNum > 0)
            {
                strSql.Append(" top " + topNum.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" from " + table_name);

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (strOrder.Trim() != "")
            {
                strSql.Append(" order by " + strOrder);
            }

            return DbHelperOleDb.Query(strSql.ToString());
        }

        //获取查询分页数据
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string strOrder, out int recordCount)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + table_name);
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            recordCount = Convert.ToInt32(DbHelperOleDb.GetSingle(PagingHelper.CreateCountingSql(sql.ToString())));
            return DbHelperOleDb.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, sql.ToString(), strOrder));
        }

        //通用
        public int ExecuteSql(string sql)
        {
            return DbHelperOleDb.ExecuteSql(sql);
        }


        //更新主治医生
        public bool UpdateDoctorName(Model.patient model)
        {
            //开始
            string sql = "";
            DateTime now = DateTime.Now;

            sql = string.Format(@"update {0} set 
                        user_name = '{1}'
                        , doctor_name = '{2}'
                        , add_time = '{3}'
                        where patient_no = '{4}'",
                        table_name, 
                        model.user_name, 
                        model.doctor_name, 
                        now, 
                        model.patient_no);            

            //更新
            int n = DbHelperOleDb.ExecuteSql(sql);
            return n > 0;
        }

        //更新阈值
        public bool UpdateThreshold(string patientNo, string thresholdA, string thresholdB)
        {
            //校验
            if(thresholdA == "-1" && thresholdB == "-1") return false;

            //开始
            string sql = "";
            DateTime now = DateTime.Now;
                        
            sql = "update " + table_name + " set ";

            sql += " add_time = '" + now.ToString("yyyy-MM-dd HH:mm:ss") + "'";

            if (thresholdA != "-1")
            {
                sql += " ,AThreshold = '" + thresholdA + "'";
            }

            if (thresholdB != "-1")
            {
                sql += " ,BThreshold = '" + thresholdB + "'";
            }
            
            sql += " where patient_no = '" + patientNo + "'";

            //更新
            int n = DbHelperOleDb.ExecuteSql(sql);
            return  n > 0;
        }
        
        //获取最近方案类型  //当然在 TMS_patient 表中增加 plan_type 字段最好  //【暂不用】
        public string GetPlanType(string patientNo)
        {
            string sql = "select plan_no from " + table_name + " where patient_no = '" + patientNo + "'";

            object oPlanNo = DbHelperOleDb.GetSingle(sql);
            if (oPlanNo == null)
            {
                return "";
            }

            string planNo = oPlanNo.ToString();
            if (planNo.Contains('T'))
            {
                return "TBS刺激";
            }
            else if (planNo.Contains('R'))
            {
                return "重复刺激";
            }
            else if (planNo.Contains('S'))
            {
                return "序列刺激";
            }
            else
            {
                return "";
            }
        }

        //获取最大编号
        public string GetMaxPatientNo(string today)
        {
            string sql = string.Format(@"select top 1 patient_no 
                                         from {0} 
                                         where patient_no like '{1}%' 
                                         order by patient_no desc",
                                         table_name, today);

            object oo = DbHelperOleDb.GetSingle(sql);

            if (oo == null)
            {
                return "";
            }
            else
            {
                return oo.ToString();
            }                
        }

    }
}
